﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class D_Hoadon:AccessData
    {
        //-----Hàm lấy ra hết------        
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_Get_List";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm lấy theo PK HoadonID------
        public M_Hoadon GetbyPK_HoadonID(int HoadonID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_GetByHoadonId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@HoadonID", HoadonID);
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                M_Hoadon M_Hoadon = null;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        M_Hoadon = new M_Hoadon();

                        M_Hoadon.HoadonID = Convert.ToInt32(dr["HoadonID"]);
                        M_Hoadon.KhachID = Convert.ToInt32(dr["KhachID"]);
                        M_Hoadon.DangkyID = Convert.ToInt32(dr["DangkyID"]);
                        if (dr["Tongtien"] != DBNull.Value)
                            M_Hoadon.Tongtien = Convert.ToDecimal(dr["Tongtien"]);
                        if (dr["Ngaythanhtoan"] != DBNull.Value)
                            M_Hoadon.Ngaythanhtoan = Convert.ToDateTime(dr["Ngaythanhtoan"]);                        
                    }
                }
                dr.Close();
                sqlCmd.Connection.Close();
                return M_Hoadon;
            }
        }

        //-----Hàm lấy theo FK------        
        public DataTable GetByFK_KhachID(int KhachID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_GetByKhachID";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@KhachID", KhachID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }
        public DataTable GetByFK_DangkyID(int DangkyID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_GetByDangkyID";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@DangkyID", DangkyID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm Insert------
        public void Insert(M_Hoadon M_Hoadon)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_Insert";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@HoadonID", SqlDbType.Int)).Value = M_Hoadon.HoadonID;
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Hoadon.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Hoadon.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@Tongtien", SqlDbType.Decimal)).Value = M_Hoadon.Tongtien;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaythanhtoan", SqlDbType.DateTime)).Value = M_Hoadon.Ngaythanhtoan;            

            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
        }

        //-----Hàm Update------
        public bool Update(M_Hoadon M_Hoadon)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_Update";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@HoadonID", SqlDbType.Int)).Value = M_Hoadon.HoadonID;
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Hoadon.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Hoadon.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@Tongtien", SqlDbType.Decimal)).Value = M_Hoadon.Tongtien;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaythanhtoan", SqlDbType.DateTime)).Value = M_Hoadon.Ngaythanhtoan;

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }

        //-----Hàm xóa------
        public bool Delete(int HoadonID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Hoadon_Delete";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@HoadonID", HoadonID));

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }
    }
}
